#coding:utf-8
from django.db import models
from django.conf import settings
from datetime import datetime
from django.contrib.auth.models import  User
# Create your models here.
########################Basic Model#############
class UserProfile(models.Model):
    class Meta:
        db_table = "user_profile"
    User_id = models.ForeignKey(User,db_column="User_id",primary_key=True)
    User_name = models.TextField(max_length=120, blank=False, null=False)
    User_alias = models.TextField(max_length=120, blank=False, null=False)
    User_cellphone = models.TextField(max_length=80, blank=False, null=False)
    User_email = models.TextField(max_length=120, blank=False, null=False)
    User_address = models.TextField(max_length=300, blank=False, null=False)
    User_status = models.TextField(max_length=120, blank=False, null=False)
    User_work_type = models.TextField(max_length=120, blank=False, null=False)
    User_type = models.TextField(max_length=120, blank=False, null=False)
    User_introduce = models.TextField(max_length=120, blank=False, null=False)
    User_pic = models.TextField(max_length=120)
    Create_time = models.DateField()
    Update_time = models.DateField(default=datetime.now)
    Remark = models.TextField(max_length=200, blank=True, null=True)




class CommReg(models.Model):
    class Meta:
        db_table = "commreg"
    Comm_id = models.AutoField(max_length=11, blank=False, null=False,primary_key=True)
    Attribute_key = models.TextField(max_length=120, blank=False, null=False)
    Attribute_value = models.TextField(max_length=120, blank=False, null=False)
    Type = models.TextField(max_length=45, blank=False, null=False)
    Status = models.TextField(max_length=45, blank=False, null=False )
    User_id = models.TextField(max_length=45)
    schema = models.TextField(max_length=45)
    description  = models.TextField(max_length=45)
    Create_time = models.DateField()
    Update_time = models.DateField(default=datetime.now)
    Remark = models.TextField(max_length=45,blank=True, null=True)



class Group(models.Model):
    class Meta:
        db_table = "group"
    group_id  = models.AutoField(blank=False, null=False,primary_key=True)
    group_name = models.TextField(blank=False, null=False)
    Create_time  = models.DateField()
    Update_time = models.DateField(auto_now=True)
    Status = models.TextField(blank=False, null=False)
    description = models.TextField(blank=False, null=False)
    Remark = models.TextField(blank=True, null=True)

class UserGroup(models.Model):
    class Meta:
        db_table = "user_group"
    group_id = models.TextField(blank=False, null=False)
    User_id = models.TextField(blank=False, null=False)
    Remark = models.TextField(blank=True, null=True)

class Language(models.Model):
    class Meta:
        db_table = "language"
    Language_id  = models.AutoField(blank=False, null=False,primary_key=True)
    Language_name = models.TextField(blank=False, null=False)
    Create_time = models.DateField()
    Update_time = models.DateField(auto_now=True)
    Remark = models.TextField(blank=True, null=True)


class UserLanguage(models.Model):
    class Meta:
        db_table = "user_language"
    Relate_id = models.AutoField(blank=False, null=False,primary_key=True)
    User_id = models.ForeignKey(UserProfile, db_column="User_id")
    Language_id = models.ForeignKey(Language, db_column="Language_id")
    Create_time = models.DateField()
    Update_time = models.DateField(auto_now=True)
    Remark = models.TextField(blank=True, null=True)

class Certificate(models.Model):
    class Meta:
        db_table = "certificate"
    Certificate_id = models.AutoField(blank=False, null=False,primary_key=True)
    Certificate_name = models.TextField(db_column="Certificate_name")
    Ref_id = models.TextField(db_column='Ref_id',blank=False, null=False)
    Certificate_type = models.CharField(db_column='Certificate_type',blank=False, null=False,max_length= 200)
    Remark = models.TextField(blank=True, null=True)

class UserCertificate(models.Model):
    class Meta:
        db_table = "user_certificate"
    Relate_id = models.AutoField(blank=False, null=False,primary_key=True)
    User_id = models.ForeignKey(UserProfile, db_column="User_id")
    Certificate_id = models.ForeignKey(Certificate,db_column='Certificate_id')
    Create_time = models.DateField(default=datetime.today())
    Update_time = models.DateField(default=datetime.today())
    Remark  = models.TextField(blank=True)




try:
    storage = settings.FILE_UPLOAD_ROOT
except AttributeError:
    storage = 'storeCV/'

#############################File upload ####################
class DUploadFile(models.Model):
    class Meta:
        db_table = "upload_file"
    storageCv = storage+"/"+'storeCV/'
    File_id = models.AutoField(primary_key=True)
    File_name = models.CharField(max_length=400,blank=False)
    File_path =models.FileField(upload_to=storageCv)
    User_id = models.ForeignKey('UserProfile', db_column="user_id")
    Type = models.CharField(max_length=120, blank=False, null=False)
    Status = models.CharField(max_length=120, blank=False, null=False)
    Create_time = models.DateTimeField()
    schema = models.TextField(blank=False, null=False)
    Remark = models.TextField(blank=True)


#############################upload CV#####################
class Resume(models.Model):
    class Meta:
        db_table = "resume"
    Resume_id = models.AutoField(primary_key=True,blank=False, null=False)
    user_id = models.ForeignKey('UserProfile', db_column="user_id")
    Language_id = models.ForeignKey('Language', db_column="Language_id")
    File_id = models.ForeignKey('DUploadFile', db_column="File_id")
    Resume_name = models.TextField(blank=False, null=False)
    Resume_status = models.ForeignKey('Status', db_column="Resume_status")
    Resume_type = models.CharField(blank=False, null=False, max_length=50)
    Create_time = models.DateTimeField(auto_now=True)
    Update_time = models.DateField(auto_now=True)
    Remark = models.TextField(blank=True)




#################message########################
class Message(models.Model):
    class Meta:
        db_table="message"
    Message_id = models.AutoField(primary_key=True,blank=False, null=False)
    From_id = models.ForeignKey('UserProfile', db_column="from_user_id" ,related_name='From_id')
    To_id  = models.ForeignKey('UserProfile', db_column="to_user_id",related_name='to_id')
    Message_type = models.CharField(blank=False, null=False,max_length=200)
    Message_content =models.CharField(blank=False, null=False,max_length=500)
    Send_Date_Time  = models.DateTimeField()
    status  = models.ForeignKey('Status', db_column="status_id")
    Parent_message_id = models.IntegerField(db_column="parent_message_id", null=True)
    Remark =models.CharField(blank=False, null=False,max_length=400)


#################状态##########################

class Status(models.Model):
    class Meta:
        db_table = "status"
    Status_id = models.AutoField(primary_key=True,blank=False, null=False)
    Status_key = models.CharField(blank=False, null=False,max_length=200)
    Status_name = models.CharField(blank=False, null=False,max_length=200)
    Status_type = models.IntegerField(blank=False, null=False,max_length=200)
    Create_time = models.DateField(auto_now=True)
    Remark = models.TextField(blank=True)


class Postion(models.Model):
    class Meta:
        db_table = "postion"
    Postion_id = models.BigIntegerField(primary_key=True,max_length=200)
    Postion_name = models.CharField(blank=False, null=False,max_length=200)
    Postion_type = models.CharField(blank=False, null=False,max_length=200)
    Remark = models.TextField(blank=True)



#####################Task center###############################


class Task(models.Model):
    class Meta:
        db_table="task"
    Task_id= models.AutoField(primary_key=True,blank=False, null=False)
    User_id  = models.ForeignKey('UserProfile', db_column="user_id")
    Language = models.ForeignKey('Language', db_column="language_id")
    Task_name =models.CharField(blank=False, null=False,max_length=400)
    Task_start_time =models.DateField(blank=False, null=False)
    Task_end_time =models.DateField(blank=False, null=False)
    Task_type = models.CharField(blank=False, null=False,max_length=400)
    Task_address = models.CharField(blank=False, null=False,max_length=200)
    Task_user_name= models.CharField(blank=False, null=False,max_length=200)
    Task_user_cellphone= models.CharField(blank=False, null=False,max_length=200)
    Prices = models.FloatField(blank=False, null=False)
    Status = models.ForeignKey('Status', db_column="status_id")
    Stage = models.CharField(blank=False, null=False,max_length=200)
    agree = models.CharField(blank=False, null=False,max_length=200)
    Create_time =models.DateTimeField(auto_now=True)
    Update_time =models.DateTimeField(auto_now=True)
    Remark = models.CharField(blank=False, null=False,max_length=400)

class Task_user(models.Model):
    class Meta:
        db_table="task_user"
    Task_id = models.ForeignKey('Task', db_column="task_id")
    Group_id = models.ForeignKey('Group', db_column="group_id")

class Lesson(models.Model):
    class Meta:
        db_table="lesson"
    Lesson_id = models.AutoField(primary_key=True,blank=False, null=False)
    Lesson_target_user_id = models.CharField(blank=False, null=False,max_length=200)
    Lesson_teacher_id = models.CharField(blank=False, null=False,max_length=200)
    Lesson_content  = models.CharField(blank=False, null=False,max_length=200)
    Lesson_address = models.CharField(blank=False, null=False,max_length=200)
    Lesson_number = models.CharField(blank=False, null=False,max_length=200)
    Lesson_from_time =models.DateTimeField()
    Lesson_to_time =models.DateTimeField()
    Lesson_ref  = models.CharField(blank=False, null=False,max_length=200)
    Remark = models.CharField(blank=False, null=False,max_length=400)

class Task_lesson(models.Model):
    class Meta:
        db_table="task_lesson"
    Task_id = models.ForeignKey('Task', db_column="task_id")
    lesson_id = models.ForeignKey('Lesson', db_column="lesson_id")
    Remark = models.CharField(blank=False, null=False,max_length=400)

class Task_payment(models.Model):
    class Meta:
        db_table="task_payment"
    payment_id = models.AutoField(primary_key=True,blank=False, null=False)
    Task_id = models.ForeignKey('Task', db_column="task_id")
    Payment_type = models.CharField(blank=False, null=False,max_length=200)
    Payment_number  = models.FloatField(blank=False, null=False,max_length=200)
    Payment_user_id  = models.CharField(blank=False, null=False,max_length=200)
    Payment_date_time  =models.DateTimeField()
    Payment_Status =models.ForeignKey('Status', db_column="status_id")
    Create_time  = models.DateTimeField()
    Remark =models.CharField(blank=False, null=False,max_length=400)


Language_CHOICES={
    ('00', '无'),
    ('001', '英语'),
    ('002', '日语'),
    ('004', '韩语'),
    ('005', '汉语')
}
class UserManager(models.Manager):
    def with_counts(self):
        from django.db import connection
        cursor = connection.cursor()
        cursor.execute("""
            SELECT p.id, p.question, p.poll_date, COUNT(*)
            FROM polls_opinionpoll p, polls_response r
            WHERE p.id = r.poll_id
            GROUP BY p.id, p.question, p.poll_date
            ORDER BY p.poll_date DESC""")
        result_list = []
        for row in cursor.fetchall():
            p = self.model(id=row[0], question=row[1], poll_date=row[2])
            p.num_responses = row[3]
            result_list.append(p)
        return result_list

